﻿Imports System.Data.SqlClient

Public Class frmProspectsArchive

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        Me.Close()
    End Sub

    Sub FillProspects()
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim Cond As String = ""
           If Me.RShowAll.Checked = True Then
                Cond = ""

            ElseIf Me.RadRegistered.Checked = True Then
                Cond = "and Status=N'Registered' "

            ElseIf Me.RActive.Checked = True Then
                Cond = "and Status=N'Active' "

            ElseIf Me.RWon.Checked = True Then
                Cond = "and Status=N'Won' "

            ElseIf Me.RLost.Checked = True Then
                Cond = "and Status=N'Lost' "

            ElseIf Me.RNonDesired.Checked = True Then
                Cond = "and Status=N'Non Desired' "

            ElseIf RadReady.Checked = True Then
                Cond = "and Status=N'Ready'"

            ElseIf RadUnderProcess.Checked = True Then
                Cond = "and Status=N'Under Process'"

            ElseIf RadRecived.Checked = True Then
                Cond = "and Status=N'Received'"

            ElseIf RadConfirmed.Checked = True Then
                Cond = "and Status=N'Firm Order'"
            Else
                Me.Cursor = Cursors.Default
                Exit Sub
            End If

            'Check access level to records (Salesman or Sales Manager)
            Cond += "and AssignedTo=N'" & CurrentUser & "'"

            Dim cmd As New SqlCommand("Select SNo,PNo,ProsNo,ProsType,Status,InsuranceCompany,InsuranceBroker,ClassofBusiness,BusinessSize" & _
                                      ",ExpiryDate,IsNull(AssignedTo,N'') AssignedTo," & _
                                      "IsNull(AssignedBy,N'') AssignedBy,IsNull(AssignedDate,N'') AssignedDate,SavedUser," & _
                                      "TransDate,Isnull(dbo.GetProspectLastUpdate(SNo),N'') LastUpdate From ProspectCases where " & _
                                      "ExpiryDate > N'" & Me.DTPFrom.Value.ToShortDateString & " 00:00:01' and " & _
                                      "ExpiryDate < N'" & Me.DTPTo.Value.ToShortDateString & " 23:59:59' " & Cond, cnn)

            Dim Reader As SqlDataReader
            Dim AssignDate, ExpiryDate As String

            Me.GridProspects.Rows.Clear()

            cnn.Open()
            Reader = cmd.ExecuteReader
            While (Reader.Read)
                If Reader.Item("AssignedDate").ToString = "1/1/1900 12:00:00 AM" Then
                    AssignDate = ""
                Else
                    AssignDate = CDate(Reader.Item("AssignedDate")).ToString("yyyy/MM/dd")
                End If

                If Reader.Item("ExpiryDate") Is DBNull.Value Then
                    ExpiryDate = ""
                Else
                    ExpiryDate = CDate(Reader.Item("ExpiryDate")).ToString("yyyy/MM/dd")
                End If

                Me.GridProspects.Rows.Add(New String() {Reader.Item("SNo"), "N", Reader.Item("ProsNo"), Reader.Item("Status"), ExpiryDate, _
                                                        Reader.Item("InsuranceCompany"), Reader.Item("InsuranceBroker"), _
                                                        Reader.Item("ClassofBusiness"), Reader.Item("BusinessSize"),
                                                        Reader.Item("AssignedTo"), Reader.Item("AssignedBy"), AssignDate, _
                                                        Reader.Item("SavedUser"), Format(CDate(Reader.Item("TransDate")), "yyyy/MM/dd"), _
                                                        CDate(Reader.Item("LastUpdate")).ToString("yyyy/MM/dd")})
            End While
            cnn.Close()

            'Check Colors
            For Each row As DataGridViewRow In Me.GridProspects.Rows
                If row.Cells(Me.DGColStatus.Index).Value = "Active" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.Yellow

                ElseIf row.Cells(Me.DGColStatus.Index).Value = "Registered" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.White

                ElseIf row.Cells(Me.DGColStatus.Index).Value = "Lost" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.Red

                ElseIf row.Cells(Me.DGColStatus.Index).Value = "Won" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.GreenYellow

                ElseIf row.Cells(Me.DGColStatus.Index).Value = "Non Desired" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.Silver

                ElseIf row.Cells(Me.DGColStatus.Index).Value = "Ready" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.Green

                ElseIf row.Cells(Me.DGColStatus.Index).Value = "Under Process" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.Fuchsia

                ElseIf row.Cells(Me.DGColStatus.Index).Value = "Lost" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.Red

                ElseIf row.Cells(Me.DGColStatus.Index).Value = "Firm Order" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.Purple

                ElseIf row.Cells(Me.DGColStatus.Index).Value = "Received" Then
                    row.Cells(Me.DGColStatus.Index).Style.BackColor = Color.Khaki

                End If
            Next

            'Check Expiry Date
            For Each Row As DataGridViewRow In Me.GridProspects.Rows
                If Now.Date >= CType(Row.Cells(4).Value, Date).Date Then
                    Row.Cells(4).Style.BackColor = Color.Red
                Else
                    Row.Cells(4).Style.BackColor = Color.White
                End If
            Next

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        FillProspects()
    End Sub

    Private Sub RShowAll_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RShowAll.CheckedChanged
        Me.GridProspects.Rows.Clear()
    End Sub

    Private Sub RActive_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RActive.CheckedChanged
        Me.GridProspects.Rows.Clear()
    End Sub

    Private Sub RNotActive_CheckedChanged(sender As System.Object, e As System.EventArgs)
        Me.GridProspects.Rows.Clear()
    End Sub

    Private Sub RWon_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RWon.CheckedChanged
        Me.GridProspects.Rows.Clear()
    End Sub

    Private Sub RLost_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RLost.CheckedChanged
        Me.GridProspects.Rows.Clear()
    End Sub

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub Button2_Click_1(sender As System.Object, e As System.EventArgs) Handles Button2.Click
        Try
            Me.Cursor = Cursors.WaitCursor

            Dim cmd As New SqlCommand
            Dim Trans As SqlTransaction

            cnn.Open()
            Trans = cnn.BeginTransaction
            cmd.Connection = cnn
            cmd.Transaction = Trans

            For Each row As DataGridViewRow In Me.GridProspects.Rows
                If row.Cells(1).Value = "Y" Then
                    cmd.CommandText = "Update ProspectCases Set Archive=0 Where SNo=" & CInt(row.Cells(0).Value)
                    cmd.ExecuteNonQuery()
                End If
            Next

            Trans.Commit()
            cnn.Close()

            FillProspects()

            Me.Cursor = Cursors.Default
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub RNonDesired_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RNonDesired.CheckedChanged
        Me.GridProspects.Rows.Clear()
    End Sub

    Private Sub frmProspectsArchive_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Me.WindowState = FormWindowState.Maximized
    End Sub
End Class